package com.shimmer.dao;

import com.shimmer.entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Package com.shimmer.dao
 * @Author shimmer
 * @Date 2020/8/16 10:27
 * @TODO
 */
public class UserDao {
    public UserDao() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    private Connection getConnection(){
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/school","root","123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    
    // 查询
    public List<User> getUsers(){
        List<User> users = new ArrayList<>();
        try(Connection c = getConnection(); Statement s = c.createStatement();){
            String sql = "select * from t_users";
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                Integer id = rs.getInt(1);
                String name = rs.getString(2);
                String password = rs.getString(3);
                User user = new User(id,name,password);
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }
    
    // 增加
    public void addUser(User user){
        String sql = "insert into `t_users`(`name`,`password`) values(?,?)";
        try(Connection c = getConnection();PreparedStatement ps = c.prepareStatement(sql);) {
            String name = user.getName();
            String password = user.getPassword();
            ps.setString(1,name);
            ps.setString(2,password);
            // 注：statement.executeUpdate(sql);
            // prepareStatement因为已经预编译了，所以不需要再传sql！！！
            ps.executeUpdate();
            // 设置id
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()){
                user.setId(rs.getInt(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

/*
    // 增加
    public void addUser(User user){
        try(Connection c = getConnection();Statement ps = c.createStatement();) {
            String name = user.getName();
            String password = user.getPassword();
            String sql = "insert into `t_users`(`name`,`password`) values('"+name+"','"+password+"')";
            int i = ps.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
*/

    // 删除
    public void deleteUserById(int id){
        String sql = "delete from `t_users` where `id`=?";
        try(Connection c = getConnection();PreparedStatement ps = c.prepareStatement(sql)){
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public User getUsersById(int id) {
        User user = new User();
        String sql = "select * from t_users where `id`=?";
        try(Connection c = getConnection();PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1,id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                user.setId(id);
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public void updateUser(User user) {
        String sql = "update t_users set `name`=?,`password`=? where `id`=?";
        try(Connection c =getConnection();PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1,user.getName());
            ps.setString(2,user.getPassword());
            ps.setInt(3,user.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    } 
}
